#region Using directives
using System;
using System.Data;
using WP.DB;
using WP.MF.DataEntity;
using WP.Commons;
#endregion

namespace WP.MF.DataAccess
{
    public class MRLPriceByCatgDAL : IDataAccess
    {
        public MRLPriceByCatgDAL()
        {
        }

        #region FieldNames
        public string FieldNames
        {
            get
            {
                return @" PriceDate, PlnNo, 
                        CatGrp1, CatGrp2, CatGrp3, CatGrp4, CatGrp5, CatGrp6, CatGrp7, CatGrp8, 
                        Price5, Price6, Price7, Price8, Price10, Price12, Price14, Price16, Price18, 
                        Price20, Price22, Price24, CurNo, Rate";
            }
        }
        #endregion

        public string SQLList(int iParameter)
        {
            string strSQL = "";

            strSQL = @"Select RecId, PriceDate, PlnNo, CurNo, Rate, CatGrp1, CatGrp2, CatGrp3, CatGrp4, CatGrp5 From mRLPriceByCatg where 1=1 " + Permission.GetSubCondition(false, "mRLPriceByCatg", iParameter) +
                       " order By PriceDate desc";

            return strSQL;
        }

        #region Select by RecID

        public string SelectSqlByRecID(string strRecID)
        {
            string strSql = @"SELECT top 1 RecId, PriceDate, PlnNo, 
                     CatGrp1, CatGrp2, CatGrp3, CatGrp4, CatGrp5, CatGrp6, CatGrp7, CatGrp8, 
                     Price5, Price6, Price7, Price8, Price10, Price12, Price14, Price16, Price18, 
                     Price20, Price22, Price24, CurNo, Rate , RecStamp
                     FROM mRLPriceByCatg
                     WHERE RecID=" + strRecID;
            return strSql;
        }
        
        public MRLPriceByCatgDEL SelectDataByRecID(DBAccess dBAccess, string strRecID)
        {
            MRLPriceByCatgDEL MRLPriceByCatgDEL = new MRLPriceByCatgDEL();
            string strSql = string.Empty;

            strSql = @"SELECT top 1 RecId, PriceDate, PlnNo, 
                    CatGrp1, CatGrp2, CatGrp3, CatGrp4, CatGrp5, CatGrp6, CatGrp7, CatGrp8, 
                    Price5, Price6, Price7, Price8, Price10, Price12, Price14, Price16, Price18, 
                    Price20, Price22, Price24, CurNo, Rate , RecStamp
                    FROM mRLPriceByCatg
                    WHERE RecID=" + strRecID;

            dBAccess.GetDataSet(strSql, MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG, MRLPriceByCatgDEL);
            return MRLPriceByCatgDEL;
        }
        #endregion

        #region Select by Key
        public MRLPriceByCatgDEL SelectDataByKey(DBAccess dBAccess, string strPriceDate, string strCurNo)
        {
            MRLPriceByCatgDEL MRLPriceByCatgDEL = new MRLPriceByCatgDEL();
            string strSql = string.Empty;
            object[] oValues;

            strSql = @"SELECT RecId, PriceDate, PlnNo, 
                        CatGrp1, CatGrp2, CatGrp3, CatGrp4, CatGrp5, CatGrp6, CatGrp7, CatGrp8, 
                        Price5, Price6, Price7, Price8, Price10, Price12, Price14, Price16, Price18, 
                        Price20, Price22, Price24, CurNo, Rate , RecStamp
                        FROM mRLPriceByCatg
                        WHERE PriceDate='{0}' and CurNo = {1}";

            oValues = new object[2];
            oValues[0] = strPriceDate;
            oValues[1] = strCurNo;
            strSql = string.Format(strSql, oValues);
            dBAccess.GetDataSet(strSql, MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG, MRLPriceByCatgDEL);
            return MRLPriceByCatgDEL;
        }


        #endregion

        #region Insert table by DataRow
        public void InsertMRLPriceByCatg(DBAccess dBAccess, MRLPriceByCatgDEL MRLPriceByCatgDEL)
        {

            string strSql = "";
            DataRow DR = MRLPriceByCatgDEL.Tables[MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG].Rows[0];

            strSql = BaseFunctions.GenerateSqlForDataAccess(FieldNames, MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG, MRLPriceByCatgDEL.Tables[0].Rows[0], FormStatus.AddNew);

            DR[MRLPriceByCatgDEL.RECID] = dBAccess.GetValue(strSql);
        }
        #endregion

        #region Update table by DataRow
        public void UpdateMRLPriceByCatg(DBAccess dBAccess, MRLPriceByCatgDEL MRLPriceByCatgDEL)
        {
            string strSql = string.Empty;

            DataRow dr = MRLPriceByCatgDEL.Tables[MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG].Rows[0];

            strSql = BaseFunctions.GenerateSqlForDataAccess(this.FieldNames, MRLPriceByCatgDEL.TABLENAME_MRLPRICEBYCATG, dr, FormStatus.Update);

            dr[MRLPriceByCatgDEL.RECSTAMP] = dBAccess.GetObjectValue(strSql);
        }
        #endregion

        #region Delete table by Key
        public void DeleteMRLPriceByCatg(DBAccess dBAccess, string strRecID)
        {
            string strSql = "";

            strSql += "delete from mRLPriceByCatg WHERE RecId = " + strRecID + ";";

            dBAccess.ExecuteSql(strSql);

        }
        #endregion
    }
}
